<?php

namespace app\statistical\controller;

use app\course\model\CourseModel;
use app\financial\model\OrderModel;
use app\student\model\StudentModel;
use app\system\model\SchoolModel;
use cmf\controller\AdminBaseController;
use think\Db;

class StatisticalController extends  AdminBaseController
{
    /*
     * 续班
     */
    public function index()
    {
        $param = $this->request->param();

        $where = [
            'c.shop_id' => ['=', $this->admin_shop_id],
            'c.delete_time' => ['eq', 0]
        ];
        $whereor=[];
        if (!empty($param['course_id']) && empty($param['school_id'])) {
            //课程名称存在 校区名字为空，不能操作
            $this->error('请同时选择校区和课程');
        }
        if (!empty($param['school_id']) && empty($param['course_id'])) {
            //课程名称存在 校区名字为空，不能操作
            $this->error('请同时选择校区和课程');
        }
        if (!empty($param['course_id']) || !empty($param['school_id'])) {
            //课程名称
            $where['co.id'] =intval($param['course_id']) ;
            $where['c.school_id'] = intval($param['school_id']);
        }
        if (!empty($param['class_id']) && count($param['class_id'])==1) {
            //一个班级名字
            $whereor= $param['class_id'];
            $this->assign('class_ids', $param['class_id']);
        }
        if (!empty($param['class_id']) && count($param['class_id'])>1 ) {
            //多个班级名字
            foreach($param['class_id'] as $k=>$v)
            {
                $whereor[] = $v;
            }
            $this->assign('class_ids', $param['class_id']);
        }

        //$wheretime()
        $wheretime=[]; //时间查询
        $homework_starttime = empty($param['homework_starttime']) ? 0 : strtotime($param['homework_starttime']);
        $homework_endtime   = empty($param['homework_endtime']) ? 0 : strtotime($param['homework_endtime']);
        if (!empty($homework_starttime) && !empty($homework_endtime)) {
            $wheretime['ct.insert_class_time'] = [['>= time', $homework_starttime], ['<= time', $homework_endtime]];
        } else {
            if (!empty($homework_starttime)) {
                $wheretime['ct.insert_class_time'] = ['>= time', $homework_starttime];
            }
            if (!empty($homework_endtime)) {
                $wheretime['ct.insert_class_time'] = ['<= time', $homework_endtime];
            }
        }
        /*
         * 学校（搜索框的校区)
         */
             $school_data=Db::name('school')
            ->where('shop_id', '=', $this->admin_shop_id)//多店铺
            ->where('delete_time', '=', 0)//未删除
            ->where('is_school', '=', 0)//1:地区 0:学校
            ->select();
        /*
         * 课程分类(搜索框的课程）
         */
        $course_data=Db::name('course')
            ->where('shop_id', '=', $this->admin_shop_id)//多店铺
            ->where('delete_time', '=', 0)//未删除
            ->select();
 //总费用现有班级的
            $total_now=0;
            $total_xu=0; //续班总费用
        //班级（搜索)或者课程分类都是空的话，搜索框的班级也不显示
        if (empty($param['course_id']) && empty($param['school_id'])) {
            //搜索框的班级
            $data_class=[];
            $data=[];
        }else {  //如果有校区和课程搜索
            //搜索框的班级
            $data_class = Db::name('class')->alias('c')
                ->field('c.id cid,c.name cname,c.current_num,c.is_class,c.enable_class_ids')
                ->join('school sc', 'c.school_id=sc.id')
                ->join('course co', 'c.course_id=co.id')
                ->where($where)
                ->where(['c.is_class' => 1, 'c.is_and_class' => 0])//存在续班的
                ->order('c.id')
                ->select()
                ->toArray();
            /*
           * 续班分析表echart的名字,$whereor多个班级
             * 重构代码，因为续报的班级2-3册之间产生一个订单，其实就是2册统计
             * 所以订单的价格重新计算，先算人数
           */
            if (!empty($whereor)) {
                $data = Db::name('class')->alias('c')->field('c.id cid,co.id course_id,
                sc.id school_id,c.name cname,ct.id ctid,
                c.is_class,c.enable_class_ids,ct.student_id student_id')
                    ->join('school sc', 'c.school_id=sc.id')
                    ->join('course co', 'c.course_id=co.id')
                    ->join('class_student ct', 'ct.class_id=c.id')
                    ->where($where)
                    ->where('c.id', 'in', $whereor)
                    ->where($wheretime)
                    ->where(['c.is_class' => 1,'ct.delete_time'=>0])//存在续班，而且是没有退课的学生
                    ->select();
                if(!$data->isEmpty())
                {
                    $data=$data->toArray();
                    foreach($data as $k=>$v)
                    {

                        $sum=0;
                        $order_data=Db::name('order')->where(['class_id'=>$v['cid'],'student_id'=>$v['student_id']])
                            ->where('order_status', ['eq', 2], ['eq', 1], 'or')
                            ->find();
//                        dump($order_data);
                        //如果是连报产生的订单,除以2或者3
                        if(!is_null($order_data) && !empty($order_data['continue_year_type']))
                        {
                            if($order_data['continue_year_type']==1)
                            {
                                $data[$k]['pay_amount']=$order_data['pay_amount'];
                            }
                            if($order_data['continue_year_type']==2)
                            {
                                $total_price=Db::name('order')->where(['id'=>$order_data['id']])->value('pay_amount');
                                $total_price=$total_price/2;
                                $data[$k]['pay_amount']=$total_price;
                            }
                            if($order_data['continue_year_type']==3)
                            {
                                $total_price=Db::name('order')->where(['id'=>$order_data['id']])->value('pay_amount');
                                $total_price=$total_price/3;
                                $data[$k]['pay_amount']=$total_price;
                            }
                        }
                        //如果不存在可能是2册连报的学生
                        //续报的转班了，并班了，统计分析
                        if(is_null($order_data))
                        {
                            //找到同课程，同校区，且产生过订单的
                            $tong_course=Db::name('class_student')->alias('ct')->field('ct.*')
                                ->join('order o','o.student_id=ct.student_id and o.class_id=ct.class_id')
                                ->where(['course_id'=>$v['course_id'],'school_id'=>$v['school_id']])->select();
                            $bing_course=Db::name('class_student')->where(['id'=>$v['ctid']])->value('old_class_id');

//                            dump($data[$k]);
                            if(!$tong_course->isEmpty())
                            { //连报和并班
                                $tong_course=$tong_course->toArray();
                                foreach($tong_course as $j=>$val)
                                {
                                    $class_ids=Db::name('class')->where(['id'=>$val['class_id']])->value('enable_class_ids');
                                    $class_id_contiue=explode(',',$class_ids)[0];
                                    if($class_id_contiue==$v['cid'])
                                    {
                                        //2年价格一半就是
                                        $total_price=Db::name('order')->where(['id'=>$val['class_id'],'student_id'=>$v['student_id']])->value('pay_amount');
//                                        dump($class_id_contiue);
//                                        dump($total_price);
                                        $total_price=$total_price/2;
                                        $data[$k]['pay_amount']=$total_price;
                                    }
                                }
                            }else{
                                //并班过来的学生统计
                                if(!empty($bing_course))
                                {
                                    //统计原来的订单
                                    $data[$k]['pay_amount']=0;
                                }
                            }
                        }
                        $data[$k]['pay_amount']=$order_data['pay_amount'];

                    }
                }
            } else {
                //没有选中班级的话，就是所有此课程，此校区的班级的统计
                $data = Db::name('class')->alias('c')->field('c.id cid,co.id course_id,
                sc.id school_id,c.name cname,ct.id ctid,
                c.is_class,c.enable_class_ids,ct.student_id student_id')
                    ->join('school sc', 'c.school_id=sc.id')
                    ->join('course co', 'c.course_id=co.id')
                    ->join('class_student ct', 'ct.class_id=c.id')
                    ->where($where)
                    ->where('c.id', 'in', $whereor)
                    ->where($wheretime)
                    ->where(['c.is_class' => 1,'ct.delete_time'=>0])//存在续班，而且是没有退课的学生
                    ->select();

                if(!$data->isEmpty())
                {
                    $data=$data->toArray();
//                    dump($data);

                    foreach($data as $k=>$v)
                    {
                        $sum=0;
                        $order_data=Db::name('order')->where(['class_id'=>$v['cid'],'student_id'=>$v['student_id']])
                            ->where('order_status', ['eq', 2], ['eq', 1], 'or')
                            ->find();
//                        dump($order_data);

                        //如果是连报产生的订单,除以2或者3
                        if(!is_null($order_data) && !empty($order_data['continue_year_type']))
                        {
                            if($order_data['continue_year_type']==1)
                            {
                                $data[$k]['pay_amount']=$order_data['pay_amount'];
                            }
                            if($order_data['continue_year_type']==2)
                            {
                                $total_price=Db::name('order')->where(['id'=>$order_data['id']])->value('pay_amount');
                                $total_price=$total_price/2;
                                $data[$k]['pay_amount']=$total_price;
                            }
                            if($order_data['continue_year_type']==3)
                            {
                                $total_price=Db::name('order')->where(['id'=>$order_data['id']])->value('pay_amount');
                                $total_price=$total_price/3;
                                $data[$k]['pay_amount']=$total_price;
                            }
                        }
                        //如果不存在可能是2册连报的学生
                        //续报的转班了，并班了，统计分析
                        if(is_null($order_data))
                        {
                            //找到同课程，同校区，且产生过订单的
                            $tong_course=Db::name('class_student')->alias('ct')->field('ct.*')
                                ->join('order o','o.student_id=ct.student_id and o.class_id=ct.class_id')
                                ->where(['course_id'=>$v['course_id'],'school_id'=>$v['school_id']])->select();
                            $bing_course=Db::name('class_student')->where(['id'=>$v['ctid']])->value('old_class_id');

//                            dump($data[$k]);
                            if(!$tong_course->isEmpty())
                            { //连报和并班
                                foreach($tong_course as $j=>$val)
                                {
                                    $class_ids=Db::name('class')->where(['id'=>$val['class_id']])->value('enable_class_ids');
                                    $class_id_contiue=explode(',',$class_ids)[0];
                                    if($class_id_contiue==$v['cid'])
                                    {
                                        //2年价格一半就是
                                        $total_price=Db::name('order')->where(['id'=>$val['class_id'],'student_id'=>$v['student_id']])->value('pay_amount');
//                                        dump($class_id_contiue);
//                                        dump($total_price);
                                        $total_price=$total_price/2;
                                        $data[$k]['pay_amount']=$total_price;
                                    }
                                }
                            }else{
                                //并班过来的学生统计
                                if(!empty($bing_course))
                                {
                                    //统计原来的订单
                                    $data[$k]['pay_amount']=0;
                                }
                            }
                        }elseif(!empty($order_data))
                        {
                            $data[$k]['pay_amount']=$order_data['pay_amount'];
                        }
                    }
                }
            }
            //续费统计
                foreach ($data as $k => $v) {
                    $is_continue_id = explode(',', $v['enable_class_ids'])[0]; //续班的id
                //总费用现有班级的
                    //续班的费用
                    $xu_pay_amount = Db::name('class')->alias('c')->field('c.id cid,co.id course_id,
                         sc.id school_id,c.name cname,
                        c.is_class,c.enable_class_ids,ct.student_id student_id')
                        ->join('school sc', 'c.school_id=sc.id')
                        ->join('course co', 'c.course_id=co.id')
                        ->join('class_student ct', 'ct.class_id=c.id')
                        ->where($where)
                        ->where(['ct.class_id'=>$is_continue_id,'ct.delete_time'=>0,'ct.student_status'=>4,'ct.student_id'=>$v['student_id']]) //存在，而且是没有退课的学生
                        ->where($wheretime)
                        ->find();
                    if(!is_null($xu_pay_amount))
                    {
                        foreach($xu_pay_amount as $j=>$val)
                        {
                            $order_data=Db::name('order')->where(['class_id'=>$xu_pay_amount['cid'],'student_id'=>$xu_pay_amount['student_id']])
                                ->where('order_status', ['eq', 2], ['eq', 1], 'or')
                                ->find();
                            //如果不存在可能是2册连报的学生,转班有订单，并班和连报没有订单,但是续班应该也会产生订单
                            if(is_null($order_data))
                            {
                                //找到同课程，同校区，
                                $tong_course=Db::name('class_student')->alias('ct')->field('ct.*')
                                    ->join('order o','o.student_id=ct.student_id and o.class_id=ct.class_id')
                                    ->where(['course_id'=>$v['course_id'],'school_id'=>$v['school_id']])
                                    ->select();
                                if(!$tong_course->isEmpty())
                                {
                                    foreach($tong_course as $j=>$val)
                                    {
                                        $class_ids=Db::name('class')->where(['id'=>$val['class_id']])->value('enable_class_ids');
                                        $class_id_contiue=explode(',',$class_ids)[0];
                                        if($class_id_contiue==$xu_pay_amount['cid'])
                                        {
                                            //2年价格一半就是
                                            $total_price=Db::name('order')->where(['id'=>$val['class_id'],'student_id'=>$v['student_id']])->value('pay_amount');
//                                        dump($class_id_contiue);
//                                        dump($total_price);
                                            $total_price=$total_price/2;

                                        }else{
                                            $total_price=0;

                                        }
                                    }
                                }
                            }elseif(!empty($order_data))
                            {
                                $total_price=$order_data['pay_amount'];
                            }
                            $data[$k]['xu_pay_amount']=$total_price;
                        }
                    }
            }
            $data_all=[];
            $data_data=[];
            $total_now=0;

            foreach($data as $k=>$v)
            {
                //班级相同的放到一起
                $cid=$v['cid'];
                $total_now+=$v['pay_amount'];
                if(!in_array($cid,$data_all))
                {
                    $count=0;
                    $sum=0;
                    $data_all['class_id']=$v['cid'];
                    $data_all['course_id']=$v['course_id'];
                    $data_all['school_id']=$v['school_id'];
                    $data_all['cname']=$v['cname'];
                    $data_all['class_id']=$v['cid'];
                    $count=1;
                    $sum+=$v['pay_amount'];

                    $GLOBALS['len']=0;
                    $GLOBALS['xu_pay_amount']=0;

                    if(isset($data['xu_pay_amount'])){
                        $GLOBALS['len']=1;
                        $GLOBALS['xu_pay_amount']=$v['xu_pay_amount'];

                    }else{
                        $GLOBALS['len']=0;
                        $GLOBALS['xu_pay_amount']=0;
                    }
                }else{
                    $count+=1;
                    $sum+=$v['pay_amount'];
                    if(isset($data['xu_pay_amount'])){
                        $GLOBALS['len']+=1;
                    }
                    if(isset($data['xu_pay_amount'])){
                        $GLOBALS['xu_pay_amount']+= $GLOBALS['xu_pay_amount'];

                    }else{
                        $GLOBALS['xu_pay_amount']= $GLOBALS['xu_pay_amount'];
                    }
                }
                if(empty($count))
                {
                    $data_all['count']="0.00%";
                }else{
                    $data_all['count']= $GLOBALS['len']/$count;
                }
                $data_all['num']=$count;
                $data_all['pay_amount']=$sum;
                $data_all['xu_pay_amount']=$GLOBALS['xu_pay_amount'];
                $data_data[$cid]=$data_all;
            }
            $data=$data_data;

        }



        $this->assign('course_data', $course_data); //课程
        $this->assign('school_data', $school_data); //校区
        $this->assign('param', $param);  //搜索条件
        $this->assign('data',json_encode($data)); //echart数据
        $this->assign('data_table',$data);  //数据
        $this->assign('data_class',$data_class); //搜索的班级
        $this->assign('total_now',$total_now); //现有班级总费用
        $this->assign('total_xu',$total_xu); //续班总费用
        return $this->fetch();
    }
    /*
   * 续班
   */
    public function indexclient()
    {
        $param = $this->request->post();

        $where = [
            'c.shop_id' => ['=', $this->admin_shop_id],
            'c.delete_time' => ['eq', 0]
        ];
        $whereor=[];
        if (!empty($param['course_id']) && empty($param['school_id'])) {
            //课程名称存在 校区名字为空，不能操作
            $this->error('请同时选择校区和课程');
        }
        if (!empty($param['school_id']) && empty($param['course_id'])) {
            //课程名称存在 校区名字为空，不能操作
            $this->error('请同时选择校区和课程');
        }
        if (!empty($param['course_id']) || !empty($param['school_id'])) {
            //课程名称
            $where['co.id'] =intval($param['course_id']) ;
            $where['c.school_id'] = intval($param['school_id']);
        }
        if (!empty($param['class_id']) && count($param['class_id'])==1) {
            //一个班级名字
            $whereor= $param['class_id'];
            $this->assign('class_ids', $param['class_id']);
        }
        if (!empty($param['class_id']) && count($param['class_id'])>1 ) {
            //多个班级名字
            foreach($param['class_id'] as $k=>$v)
            {
                $whereor[] = $v;
            }
            $this->assign('class_ids', $param['class_id']);
        }

        //$wheretime()
        $wheretime=[]; //时间查询
        $homework_starttime = empty($param['homework_starttime']) ? 0 : strtotime($param['homework_starttime']);
        $homework_endtime   = empty($param['homework_endtime']) ? 0 : strtotime($param['homework_endtime']);
        if (!empty($homework_starttime) && !empty($homework_endtime)) {
            $wheretime['ct.insert_class_time'] = [['>= time', $homework_starttime], ['<= time', $homework_endtime]];
        } else {
            if (!empty($homework_starttime)) {
                $wheretime['ct.insert_class_time'] = ['>= time', $homework_starttime];
            }
            if (!empty($homework_endtime)) {
                $wheretime['ct.insert_class_time'] = ['<= time', $homework_endtime];
            }
        }
        /*
         * 学校（搜索框的校区)
         */
        $school_data=Db::name('school')
            ->where('shop_id', '=', $this->admin_shop_id)//多店铺
            ->where('delete_time', '=', 0)//未删除
            ->where('is_school', '=', 0)//1:地区 0:学校
            ->select();
        /*
         * 课程分类(搜索框的课程）
         */
        $course_data=Db::name('course')
            ->where('shop_id', '=', $this->admin_shop_id)//多店铺
            ->where('delete_time', '=', 0)//未删除
            ->select();
        //总费用现有班级的
        $total_now=0;
        $total_xu=0; //续班总费用
        //班级（搜索)或者课程分类都是空的话，搜索框的班级也不显示
        if (empty($param['course_id']) && empty($param['school_id'])) {
            //搜索框的班级
            $data_class=[];
            $data=[];
        }else {  //如果有校区和课程搜索
            //搜索框的班级
            $data_class = Db::name('class')->alias('c')
                ->field('c.id cid,c.name cname,c.current_num,c.is_class,c.enable_class_ids')
                ->join('school sc', 'c.school_id=sc.id')
                ->join('course co', 'c.course_id=co.id')
                ->where($where)
                ->where(['is_class' => 1, 'is_and_class' => 0])//存在续班的
                ->order('c.id')
                ->select()
                ->toArray();

            /*
           * 续班分析表echart的名字,$whereor多个班级
           */
            if (!empty($whereor)) {
                $data = Db::name('class')->alias('c')->field('c.id cid,c.name cname,count(ct.id) num,sum(o.pay_amount) pay_amount,c.is_class,c.enable_class_ids')
                    ->join('school sc', 'c.school_id=sc.id')
                    ->join('course co', 'c.course_id=co.id')
                    ->join('class_student ct', 'ct.class_id=c.id')
                    ->join('order o', 'c.id=o.class_id and ct.student_id=o.student_id')
                    ->where(['o.order_status'=>'2'])
                    ->whereOr(['o.order_status'=>'1'])
                    ->where($where)
                    ->where('c.id', 'in', $whereor)
                    ->where($wheretime)
                    ->where(['is_class' => 1, 'is_and_class' => 0])//存在续班
                    ->group('c.id')
                    ->select();
            } else { //没有选中班级的话，就是所有此课程，此校区的班级的统计
                $data = Db::name('class')->alias('c')->field('c.id cid,c.name cname,count(ct.id) num,sum(o.pay_amount) pay_amount,c.is_class,c.enable_class_ids')
                    ->join('school sc', 'c.school_id=sc.id')
                    ->join('course co', 'c.course_id=co.id')
                    ->join('class_student ct', 'ct.class_id=c.id')
                    ->join('order o', 'c.id=o.class_id and ct.student_id=o.student_id')
                    ->where(['o.order_status'=>'2'])
                    ->whereOr(['o.order_status'=>'1'])
                    ->where($where)
                    ->where($wheretime)
                    ->where(['is_class' => 1, 'is_and_class' => 0])//存在续班的
                    ->group('c.id')
                    ->select();
            }

            if (!$data->isEmpty()) {
                $data = $data->toArray();
                foreach ($data as $k => $v) {
                    $is_continue_id = explode(',', $v['enable_class_ids'])[0]; //续班的id
                    $total_now+=intval($v['pay_amount']);             //总费用现有班级的
                    //原班级结课后，三个月之内
                    $last_day=Db::name('course_scheme')->where(['class_id'=>$v['id']])->order('date desc')->limit(1);
                    $three_month=$last_day+90*24*3600;
                    $count=Db::query('select count(DISTINCT(o.student_id)) from cmf_order as o
                            INNER JOIN cmf_order as oo
                            on o.student_id=oo.student_id
                            where o.order_status=1 and oo.order_status=1
                            and o.update_time>oo.update_time+3600*60*90');

                    //原班级没有人数的
                    if (empty($v['num'])) {
                        $data[$k]['count'] = '0.00%'; //比率是0
                    } else {
                        $count = number_format($count / $v['num'], 2); //续班的人数/原班级总人数
                        $data[$k]['count'] = $count * 100;
                    }
                    //续班的费用
                    $xu_pay_amount = Db::name('class')->alias('c')
                        ->join('school sc', 'c.school_id=sc.id')
                        ->join('course co', 'c.course_id=co.id')
                        ->join('class_student ct', 'ct.class_id=c.id')
                        ->join('order o', 'c.id=o.class_id and ct.student_id=o.student_id')
                        ->where(['o.order_status'=>'1'])
                        ->whereOr(['o.order_status'=>2])
                        ->where($where)
                        ->where(['o.class_id'=>$is_continue_id,'ct.student_status'=>4])
                        ->where($wheretime)
                        ->group('c.id')
                        ->sum('pay_amount');
                    $data[$k]['xu_pay_amount'] = $xu_pay_amount;
                    $total_xu+=intval($xu_pay_amount);             //总费用现有班级的

                }
            }

        }



        $this->assign('course_data', $course_data); //课程
        $this->assign('school_data', $school_data); //校区
        $this->assign('param', $param);  //搜索条件
        $this->assign('data',json_encode($data)); //echart数据
        $this->assign('data_table',$data);  //数据
        $this->assign('data_class',$data_class); //搜索的班级
        $this->assign('total_now',$total_now); //现有班级总费用
        $this->assign('total_xu',$total_xu); //续班总费用
        return $this->fetch();
    }

    /**
     * 学员人数统计
     * @return mixed
     */
    public function allStudents()
    {
        $param = $this->request->param();
        $classes = [];
        $where = [];
        $where['cl.shop_id'] = ['eq', $this->admin_shop_id];
        $where['co.shop_id'] = ['eq', $this->admin_shop_id];
        $where['cr.delete_time'] = ['eq', 0];
        $where['co.delete_time'] = ['eq', 0];
        $where['cl.delete_time'] = ['eq', 0];

        if(!empty($param['start']) && !empty($param['end'])) {
            //报名时间
            $start = strtotime($param['start']);
            $end = strtotime('+23 hours 59 minutes 59 seconds', strtotime($param['end']));
            if($start <= $end){
                $ctwhere1['ct.insert_class_time'] = [['>= time', $start], ['<= time', $end]];
            }
        } else {
            if (!empty($param['start'])) {
                $ctwhere1['ct.insert_class_time'] = ['>= time', strtotime($param['start'])];
            }
            if (!empty($param['end'])) {
                $end = strtotime('+23 hours 59 minutes 59 seconds', strtotime($param['end']));
                $ctwhere1['ct.insert_class_time'] = ['<= time', $end];
            }
        }


        if (!empty($param['school_id']) && is_numeric($param['school_id'])) {
            //(搜索栏)获取班级
            $class = Db::name('class')
                ->where('shop_id', '=', $this->admin_shop_id)
                ->where('delete_time', '=', 0)
                ->where('school_id', '=', trim($param['school_id']))
                ->field('id,name')
                ->select();
            if (!$class->isEmpty()) {
                $classes = $class;
            }
            //学校
            $where['sh.school_id'] = ['eq',intval($param['school_id'])];

            if (!empty($param['class_id']) && is_numeric($param['class_id'])) {
                //班级
                $where['sh.class_id'] = ['eq',intval($param['class_id'])];
            }

            if (!empty($param['course_id']) && is_numeric($param['course_id'])) {
                //课程
                $where['cc.course_id'] = ['eq',intval($param['course_id'])];
            }

            $where['sh.delete_time'] = ['eq', 0];

            //获取学校下班级教室课程相关信息
            $totalByClass = Db::name('class_school')->alias('sh')
                ->field('sh.class_id,cl.name,cl.classroom_id,cr.student_nums')
                ->join('class cl', 'sh.class_id=cl.id')
                ->join('classroom cr', 'cl.classroom_id=cr.id')
                ->join('class_course cc', 'sh.class_id=cc.class_id')
                ->join('course co', 'cc.course_id=co.id')
                ->where($where)
                ->select();

            if(!$totalByClass->isEmpty()){
                $totalByClass = $totalByClass->toArray();
                //班级学生关联
                $ctwhere2['ct.shop_id'] = ['eq', $this->admin_shop_id];
                $ctwhere2['ct.delete_time'] = ['eq', 0];
                if(!empty($ctwhere1)){
                    $ctwhere = array_merge($ctwhere1,$ctwhere2);
                }

                //班级座位关联
                $cswhere['cs.shop_id'] = ['eq', $this->admin_shop_id];
                $cswhere['cs.delete_time'] = ['eq', 0];
                $cswhere['cs.student_id'] = ['eq', 0];

                foreach ($totalByClass as &$v){
                    //已报名人数
                    $ctwhere['ct.class_id'] = ['eq', $v['class_id']];
                    $in_total = Db::name('class_student')->alias('ct')->where($ctwhere)->count();
                    $v['in_total'] = empty($in_total) ? 0 : intval($in_total);

                    //预留人数
                    $cswhere['cs.classes_id'] = ['eq', $v['class_id']];
                    $rs_total = Db::name('class_seat')->alias('cs')->where($cswhere)->count();
                    $v['rs_total'] = empty($rs_total) ? 0 : intval($rs_total);

                    //班级招生率
                    $new_total = intval($in_total);
                    $all_total = intval($v['student_nums']);
                    if($new_total <= $all_total){
                        $per_ratio = number_format($new_total/$all_total,4)*100..'%';
                    }else{
                        $per_ratio = '0.00%';
                    }
                    $v['total'] = $new_total;
                    $v['per_ratio'] = $per_ratio;
                }
            }

            $students = $totalByClass;
            //图例和横坐标
            $legend = '班级';

        }else{
            if (!empty($param['course_id']) && is_numeric($param['course_id'])) {
                //课程
                $where['cc.course_id'] = ['eq',intval($param['course_id'])];
                //按课程分类
                $totalByCourse = Db::name('course')->alias('co')
                    ->field('cc.course_id,cc.class_id,cl.name,cr.student_nums')
                    ->join('class_course cc', 'co.id=cc.course_id')
                    ->join('class cl', 'cl.id=cc.class_id')
                    ->join('classroom cr', 'cl.classroom_id=cr.id')
                    ->where($where)
                    ->select();

                if(!$totalByCourse->isEmpty()){
                    $totalByCourse = $totalByCourse->toArray();
                    //班级学生关联
                    $ctwhere2['ct.shop_id'] = ['eq', $this->admin_shop_id];
                    $ctwhere2['ct.delete_time'] = ['eq', 0];
                    if(!empty($ctwhere1)){
                        $ctwhere = array_merge($ctwhere1,$ctwhere2);
                    }

                    //班级座位关联
                    $cswhere['cs.shop_id'] = ['eq', $this->admin_shop_id];
                    $cswhere['cs.delete_time'] = ['eq', 0];
                    $cswhere['cs.student_id'] = ['eq', 0];

                    foreach ($totalByCourse as &$v){
                        //已报名人数
                        $ctwhere['ct.class_id'] = ['eq', $v['class_id']];
                        $in_total = Db::name('class_student')->alias('ct')->where($ctwhere)->count();
                        $v['in_total'] = empty($in_total) ? 0 : intval($in_total);

                        //预留人数
                        $cswhere['cs.classes_id'] = ['eq', $v['class_id']];
                        $rs_total = Db::name('class_seat')->alias('cs')->where($cswhere)->count();
                        $v['rs_total'] = empty($rs_total) ? 0 : intval($rs_total);

                        //班级招生率
                        $new_total = intval($in_total);
                        $all_total = intval($v['student_nums']);
                        if($new_total <= $all_total){
                            $per_ratio = number_format($new_total/$all_total,4)*100..'%';
                        }else{
                            $per_ratio = '0.00%';
                        }
                        $v['total'] = $new_total;
                        $v['per_ratio'] = $per_ratio;
                    }
                }

                $students = $totalByCourse;
                //图例和横坐标
                $legend = '班级';

            }else{
                //按学校分类
                $where['sc.shop_id'] = ['eq', $this->admin_shop_id];
                $where['sc.delete_time'] = ['eq', 0];
                $where['sc.is_school'] = ['eq', 0];
                $data = [];

                $totalBySchool = Db::name('school')->alias('sc')
                    ->field('sc.id,sc.name,sh.class_id,cr.student_nums')
                    ->join('class_school sh', 'sh.school_id=sc.id')
                    ->join('class cl', 'sh.class_id=cl.id')
                    ->join('classroom cr', 'cl.classroom_id=cr.id')
                    ->join('class_course cc', 'sh.class_id=cc.class_id')
                    ->join('course co', 'cc.course_id=co.id')
                    ->where($where)
                    ->select();

                if(!$totalBySchool->isEmpty()){
                    $totalBySchool = $totalBySchool->toArray();
                    //班级学生关联
                    $ctwhere2['ct.shop_id'] = ['eq', $this->admin_shop_id];
                    $ctwhere2['ct.delete_time'] = ['eq', 0];
                    if(!empty($ctwhere1)){
                        $ctwhere = array_merge($ctwhere1,$ctwhere2);
                    }

                    //班级座位关联
                    $cswhere['cs.shop_id'] = ['eq', $this->admin_shop_id];
                    $cswhere['cs.delete_time'] = ['eq', 0];
                    $cswhere['cs.student_id'] = ['eq', 0];

                    foreach ($totalBySchool as &$v){
                        //已报名人数
                        $ctwhere['ct.class_id'] = ['eq', $v['class_id']];
                        $in_total = Db::name('class_student')->alias('ct')->where($ctwhere)->count();
                        $v['in_total'] = empty($in_total) ? 0 : intval($in_total);

                        //预留人数
                        $cswhere['cs.classes_id'] = ['eq', $v['class_id']];
                        $rs_total = Db::name('class_seat')->alias('cs')->where($cswhere)->count();
                        $v['rs_total'] = empty($rs_total) ? 0 : intval($rs_total);

                        //已报总数
                        $new_total = intval($in_total);
                        $v['total'] = $new_total;

                    }

                    //统计各校区下的报名人数、预留人数和招生总数
                    $data = [];
                    foreach ($totalBySchool as &$item){
                        if(!isset($data[$item['id']])){
                            $data[$item['id']] = $item;
                        }else{
                            $data[$item['id']]['student_nums'] += $item['student_nums'];
                            $data[$item['id']]['in_total'] += $item['in_total'];
                            $data[$item['id']]['rs_total'] += $item['rs_total'];
                            $data[$item['id']]['total'] += $item['total'];
                        }
                    }

                    //统计各个学校的招生率
                    foreach ($data as &$val){
                        //班级招生率
                        $new_total = intval($val['total']);
                        $all_total = intval($val['student_nums']);
                        if($new_total <= $all_total){
                            $per_ratio = number_format($new_total/$all_total,4)*100..'%';
                        }else{
                            $per_ratio = '0.00%';
                        }
                        $val['per_ratio'] = $per_ratio;
                    }
                }

                $students = $data;
                //图例和横坐标
                $legend = '学校';
            }
        }

        if(count($students) > 0){
            //报名总数
            $value = array_sum(array_column($students,'total'));
            //招生总数
            $student_total = array_sum(array_column($students,'student_nums'));
            //报名比率
            if($student_total <= 0){
                $ratio = '0.00%';
            }else{
                $ratio = number_format($value/$student_total,4)*100..'%';
            }
        }else{
            $students = [];
            $value = 0;
            $ratio = '0.00%';
            $student_total = 0;
        }

        //获取学校
        $school_mdl = new SchoolModel();
        $schools = $school_mdl->getAllSchool($this->admin_shop_id);
        //获取课程
        $course_mdl = new CourseModel();
        $courses = $course_mdl->getAllCourse($this->admin_shop_id);

        $this->assign('param', $param);
        $this->assign('ratio', $ratio);
        $this->assign('value', $value);
        $this->assign('legend', $legend);
        $this->assign('student_total', $student_total);
        $this->assign('json_data', json_encode($students));
        $this->assign('data', $students);
        $this->assign('school', $schools);
        $this->assign('class', $classes);
        $this->assign('course', $courses);
        return $this->fetch();
    }

}